package org.tof.hotel.dao;

import org.tof.hotel.dto.UserDTO;
import org.tof.hotel.jdbc.JConnection;
import org.tof.hotel.jdbc.Pool;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl {

    private Pool pool;

    public UserDaoImpl() {
        this.pool = new Pool();
    }

    public UserDaoImpl(Pool pool) {
        this.pool = pool;
    }

    /**
     * Insert record about User object to the db
     * @param dto data transfer object specifying User parameters
     * @return id of inserted record
     * @throws java.sql.SQLException if a database access error occurs
     * @see org.tof.hotel.dto.AdministratorDTO
     */
    public int insert(UserDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();

        PreparedStatement prepStat = jConn.prepareStatement("INSERT INTO user (email, password) VALUES (?,?);");
        prepStat.setString(1, dto.getEmail());
        prepStat.setString(2, dto.getPassword());
        prepStat.executeUpdate();

        pool.releaseConnection(jConn);
        ResultSet rs = prepStat.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }

/**
     * Update the record in db about an <code>User</code>
     * @param dto data transfer object specifying <code>User</code> parameters
     * @return modified rows count
     * @throws SQLException if a database access error occurs
     * @see org.tof.hotel.dto.UserDTO
     */
    public int update(UserDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();

        PreparedStatement prepStat = jConn.prepareStatement("UPDATE administrator SET email=?, password=? WHERE id=?;");
        prepStat.setString(1,dto.getEmail());
        prepStat.setString(2,dto.getPassword());
        prepStat.setInt(3,dto.getId());

        int result = prepStat.executeUpdate();
        pool.releaseConnection(jConn);
        return result;
    }

    /**
     * Returns <code>UserDTO</code> with the specified Email
     * @param email user's email
     * @return dto of the specified user if user with such email exists, otherwise - <code>null</code>
     * @throws java.sql.SQLException if a database access error occurs
     * @see org.tof.hotel.dto.UserDTO
     */
    public UserDTO findByEmail(String email) throws SQLException {
        JConnection jConn = pool.getConnection();

        ResultSet rs = jConn.prepareStatement("SELECT FROM user WHERE email=" + email + ";").executeQuery();

        pool.releaseConnection(jConn);
        if (rs.next()) {
            return new UserDTO(rs.getInt("id"), rs.getString("email"), rs.getString("password"));
        }
        else {
            return null;
        }
    }

    /**
     * Deletes the record about specified <code>User</code> from the db
     * @param dto data transfer object specifying an <code>User</code>
     * @throws SQLException if a database access error occurs
     * @see org.tof.hotel.dto.UserDTO
     */
    public void remove(UserDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();

        jConn.prepareStatement("REMOVE FROM user WHERE id=" + dto.getId() + ";").executeUpdate();

        pool.releaseConnection(jConn);
    }

}
